Accessing SQL Databases

Connecting to a Postgres database#

To connect to a Postgres database will require using a database driver for Postgres. The currently recommended third-party package is github.com/jackc/pgx. This package implements a SQL driver for database/sql and provides its own methods/types for Postgres-specific features.

The choice to use database/sql or Postgres-specific types will depend on whether we need to ensure compatibility between different databases. Using database/sql allows us to write functions that work on any SQL database, while using Postgres-specific features removes compatibility and makes migration to another database more difficult. We'll discuss how to perform our examples using both methods. Here is how to connect using a standard SQL package without extra Postgres features:

Here, we open a connection to Postgres using the pgx driver that will be registered when we import the following package:

This is an anonymous import, meaning we are not using stdlib directly. This is done when we want a side effect, such as when registering a driver with the database/sql package.

The Open() call doesn't test our connection. We'll see conn.PingContext() to test that we'll be able to make calls to the database. When we want to use pgx-specific types for Postgres, the setup is slightly different, starting with a different package import:

To create that connection, use the following code:

This uses a connection pool to connect to the database for performance. We'll notice that we don't have a PingContext() call, as the native connection tests the connection as part of Connect(). Now that we know how to connect to Postgres, let's look at how we can make queries.

Querying a Postgres database#

Let's consider making a call to our SQL database to fetch some information about a user that is held in a table.

This example does the following:

• Lines 18–22: Creates UserRec to store SQL data for a user.

• Line 26: Creates a query statement called query.

• Line 28: Queries our database for a user with the requested ID.

• Line 29–33: Returns UserRec and an error if we had one.

We can increase the efficiency of this example by using a prepared statement in an object instead of just a function:

This example does the following:

• Line 1: Creates a reusable object.

• Line 3: Stores *sql.Stmt, which increases the efficiency when doing repeated queries.

• Lines 6–10: Defines a NewStorage constructor that creates our object.

Because of the generic nature of using the standard library, in these examples, any implementation of *sql.DB could be used. Switching Postgres for MariaDB would work as long as MariaDB had the same table names and format. If we use the Postgres-specific library, the same code is written like this:

This implementation looks and works in a similar way to the standard library. But the conn object here is a different, non-interface pgxpool.Conn type and not sql.Conn. And while the functionality looks similar, the pgxpool.Conn object supports queries with Postgres-specific types and syntax, such as jsonb, that sql.Conn does not.

There is no need to use a prepared statement for non-transactions when using Postgres-specific calls. The call information is automatically cached. The preceding example was simplistic in that we were pulling a specific entry. What if we wanted to also have a method to retrieve all users with IDs between two numbers? We could define this using the standard library:

The Postgres-specific syntax is the same; it just switches s.usersBetweenStmt() for conn.QueryRow().

Null values#

SQL has a concept of null values for basic types such as boolean, string, and int32. Go doesn't have the convention; instead, it provides zero values for those types. When SQL allows a column to have a null value, the standard library provides special null types in database/sql:

• sql.NullBool

• sql.NullByte

• sql.NullFloat64

• sql.NullInt16

• sql.NullInt32

• sql.NullInt64

• sql.NullString

• sql.NullTime

When we design our schema, it is better to use zero values instead of null values. But sometimes, we need to tell the difference between a value being set and the zero value. In those cases, we can use these special types in place of the standard type. For example, if our UserRec could have a null DisplayName, we can change the string type to sql.NullString:

Writing data to Postgres#

Writing data into a database is simple but requires some consideration of the syntax. The two major operations that a user wants when writing data are as follows:

• Updating an existing entry.

• Inserting a new entry.

In standard SQL, we cannot do an “update entry if it exists; insert if not” operation. As this is a common operation, each database offers some way to do this with its own special syntax. When using the standard library, we must choose between doing an update or an insert. If we do not know whether the entry exists, we'll need to use a transaction, which we'll detail in a bit.

Doing an update or insert is simply using a different SQL syntax and the ExecContext() call:

In this example, we have added two methods:

• Lines 1–9: AddUser() adds a new user to the system.

• Lines 11–18: UpdateDisplayName() updates the display name of a user with a specific ID.

Both use the sql.Stmt type, which would be a field in the object, similar to getUserStmt. The major difference when implementing using the Postgres-native package is the method name that is called and the lack of a prepared statement. Implementing AddUser() would look like the following:

Sometimes, it is not enough to just do a read or a write to the database. Sometimes, we need to do multiple actions atomically and treat them as a single action. So, in the next section, we'll talk about how to do this with transactions.

Transactions#

Transactions provide a sequence of SQL operations that are executed on the server as one piece of work. This is commonly used to provide some type of atomic operation where a read and a write are required or to extract data on a read before doing a write.

Transactions are easy to create in Go. Let's create an AddOrUpdateUser() call that will look to see whether a user exists before adding or updating our data:

This example does the following:

• Line 8: Creates a transaction with an isolation level of LevelSerializable.

• Lines 13–19: Uses a defer statement to determine whether we had an error. If we did, we roll back the entire transaction. If not, we attempt to commit the transaction.

• Line 21: Queries to find whether the user exists. It determines this by checking the error type. If the error is sql.ErrNoRows, we did not find the user. If the error is anything else, it was a system error.

• Line 24: Executes an insert statement if we don't find the user.

• Line 32: Executes an update statement if we do find the user.

The keys to a transaction are the following:

• conn.BeginTx, which starts the transaction.

• tx.Commit(), which commits our changes.

• tx.Rollback(), which reverts our changes.

A defer statement is an excellent way to handle either Commit() or Rollback() once the transaction has been created. It ensures that when the function ends, either one or the other is executed.

The isolation level is important for a transaction as it affects the performance and reliability of our system. Go provides multiple levels of isolation; however, not all database systems will support all levels of isolation.

Postgres-specific types#

So far in our examples, we have shown how to use both the standard library and Postgres-specific objects to interact with Postgres. But we haven't really shown a compelling reason to use Postgres objects.

Postgres objects shine when we need to use types or capabilities that aren't a part of the SQL standard. Let's rewrite our transaction example, but instead of storing data across standard columns, let's have our Postgres database only have two columns:

• An ID of the int type.

• Data of the jsonb type.

jsonb is not part of the SQL standard and cannot be implemented with the standard SQL library. jsonb can greatly simplify our life, as it allows us to store JSON data while querying using JSON fields:

This example is different in a few ways:

• It has additional AccessMode and DeferableMode parameters.

• We can pass our object, UserRec, as our Data jsonb column.

The access and deferable modes add extra constraints that are not available directly with the standard library. Using jsonb is a boon. Now, we can do searches on our tables with WHERE clauses that can filter on the jsonb field values. We'll also notice that pgx is smart enough to know our column type and automatically convert our UserRec into JSON.

Other options#

Besides the standard library and database-specific packages are Object-Relational Mappings (ORMs). ORMs are a popular model for managing data between our services and data storage. Go's most popular ORM is called GORM. Another popular framework that also includes support for REST and web services is
Beego.

Storage abstractions#

Many developers are tempted to use storage systems directly in their code, passing around a connection to a database. This is not optimal in that it can cause problems when we need to do the following:

• Add caching layers before storage access.

• Migrate to a new storage system for our service.

Abstracting storage behind an internal application programming interface (API) of interfaces will allow us to change storage layers later by simply implementing the interfaces with the new backend. We can then plug in the new backend at any time.

A simple example of this might be adding an interface for getting user data:

This interface allows us to implement our storage backend using Postgres, local files, SQLite, Azure Cosmos DB, in-memory data structures, or any other storage medium. This has the benefit of allowing migration from one storage medium to another by plugging in a new implementation. As a side benefit, we can decouple tests from using a database. Instead, most tests can use an in-memory data structure. This allows us to test our functionality without bringing up and tearing down infrastructure, which would be necessary with a real database.

Adding a cache layer becomes a simple exercise of writing a UserStorage implementation that calls the cache on reads and, when not found, calls our data store implementation. We can replace the original, and everything keeps working.

Note that everything described here for abstraction behind an interface applies to access to service data. A SQL API should only be used for our application to store and read data. Other services should use a stable RPC interface. This provides the same type of abstraction, allowing us to move data backends without migrating users.

So far in this section, we have learned about how to use database/sql to access generic data stores and Postgres specifically. We learned how to read and write to Postgres and implement transactions. The benefits of using database/sql versus a database-specific library such as pgx were discussed. And finally, we showed how hiding your implementations behind interface abstractions can allow us to change storage backends more easily and test code relying on storage hermetically.